﻿Imports System
Imports System.IO
Imports System.Configuration
Imports System.Data.SqlClient

Public Class DataWriter
    Implements IServiceDatabase

    Private Property _ServiceSQLServer As String = "YourServer\YourDatabase"
    Private Property _ServiceDatabase As String = "VOEISPushService"
    Private Property _ServiceAuthentication As String = ""
    Private Property _ServiceUserName As String = ""
    Private Property _ServicePassword As String = ""
    Private Property _ServiceConnectionString As String
    Private Property _ServiceMaxRecords As Integer = 100
    Private Property _ServiceDBTimeout As Integer = 300
    Private Property _ServiceLastRecordDateTime As DateTime = "1/1/1900 00:00:00"
    Private Property _SyncID As Integer


    Public Property ServiceSQLServer As String Implements IServiceDatabase.ServiceSQLServer
        Get
            Return _ServiceSQLServer
        End Get
        Set(ByVal value As String)
            _ServiceSQLServer = value
            SetConnectionString()
        End Set
    End Property

    Public Property ServiceDatabase As String Implements IServiceDatabase.ServiceDatabase
        Get
            Return _ServiceDatabase
        End Get
        Set(ByVal value As String)
            _ServiceDatabase = value
            SetConnectionString()
        End Set
    End Property

    Public Property ServiceAuthentication As String Implements IServiceDatabase.ServiceAuthentication
        Get
            Return _ServiceAuthentication
        End Get
        Set(ByVal value As String)
            _ServiceAuthentication = value
        End Set
    End Property

    Public Property ServiceUserName As String Implements IServiceDatabase.ServiceUserName
        Get
            Return _ServiceUserName
        End Get
        Set(ByVal value As String)
            _ServiceUserName = value
            SetConnectionString()
        End Set
    End Property

    Public Property ServicePassword As String Implements IServiceDatabase.ServicePassword
        Get
            Return "********"
        End Get
        Set(ByVal value As String)
            _ServicePassword = value
            SetConnectionString()
        End Set
    End Property

    Public Property ServiceConnectionString As String Implements IServiceDatabase.ServiceConnectionString
        Get
            If _ServiceUserName <> "" And _ServicePassword <> "" Then
                Return "Data Source=" & _ServiceSQLServer & ";Initial Catalog=" & _ServiceDatabase & ";User Id=" & _ServiceUserName & ";Password=********;"
            Else
                Return "Data Source=" & _ServiceSQLServer & ";Initial Catalog=" & _ServiceDatabase & ";Integrated Security=SSPI;"
            End If
        End Get
        Set(ByVal value As String)
            SetConnectionString()
        End Set
    End Property

    Public Sub SetConnectionString() Implements IServiceDatabase.SetConnectionString
        If String.IsNullOrEmpty(_ServiceUserName) Or String.IsNullOrEmpty(_ServicePassword) Then
            _ServiceConnectionString = "Data Source=" & _ServiceSQLServer & ";Initial Catalog=" & _ServiceDatabase & ";Integrated Security=SSPI;"
            _ServiceAuthentication = "Windows"
        Else
            _ServiceConnectionString = "Data Source=" & _ServiceSQLServer & ";Initial Catalog=" & _ServiceDatabase & ";User Id=" & _ServiceUserName & ";Password=" & _ServicePassword & ";"
            _ServiceAuthentication = "SQL"
        End If
    End Sub

    Public Property ServiceMaxRecords As Integer Implements IServiceDatabase.ServiceMaxRecords
        Get
            Return _ServiceMaxRecords
        End Get
        Set(ByVal value As Integer)
            _ServiceMaxRecords = value
        End Set
    End Property

    Public Property ServiceDBTimeout As Integer Implements IServiceDatabase.ServiceDBTimeout
        Get
            Return _ServiceDBTimeout
        End Get
        Set(ByVal value As Integer)
            _ServiceDBTimeout = value
        End Set
    End Property

    Public Property ServiceLastRecordDateTime As DateTime Implements IServiceDatabase.ServiceLastRecordDateTime
        Get
            Return _ServiceLastRecordDateTime
        End Get
        Set(ByVal value As DateTime)
            _ServiceLastRecordDateTime = value
        End Set
    End Property

    Public Property SyncID As Integer
        Get
            Return _SyncID
        End Get
        Set(ByVal value As Integer)
            _SyncID = value
        End Set
    End Property

    Public Sub New(ByVal SyncID As Integer)
        _SyncID = SyncID
        _ServiceSQLServer = My.Settings.ServiceSQLServer
        _ServiceDatabase = My.Settings.ServiceDatabase
        _ServiceAuthentication = My.Settings.ServiceAuthentication
        _ServiceUserName = My.Settings.ServiceUserName
        _ServicePassword = My.Settings.ServicePassword
        _ServiceMaxRecords = My.Settings.ServiceMaxRecords
        _ServiceDBTimeout = My.Settings.DBTimeout
        SetConnectionString()
    End Sub

    ''' <summary>
    ''' Updates the sync metadata in the database sync table.
    ''' </summary>
    ''' <remarks>Sync metadata should be updated at the end of every successful transmission to the hub.</remarks>
    Public Sub UpdateSyncMetadata(ByVal TableName As String, ByVal LastRecordDateTime As String, ByVal Response As String)

        If Not Response.Contains("success") Then
            'Set the LastRecordDateTime
            GetServiceLastRecordDateTime()

            'Set our variable to the database value since there was a failure.
            LastRecordDateTime = ServiceLastRecordDateTime

            'try sending an email
            Try
                Dim mailer As New SendMail()
                mailer.SendServiceMail("VOEIS Hub Push Service - Upload Failure", "Error uploading data for " & TableName & " to the hub: " & Environment.NewLine & Response)
            Catch ex As Exception
                System.Diagnostics.EventLog.WriteEntry("VOEIS Hub Push Service", "Error sending mail from the DataWriter class after push failure for file " & TableName & "." & Environment.NewLine & ex.Message.ToString, EventLogEntryType.Error)
            End Try
        End If

        Try
            Using conn As New SqlConnection(_ServiceConnectionString)
                'Open the database connection and execute our statement
                conn.Open()
                Using cmd As New SqlCommand("dbo.usp_UpdateSyncMetadata", conn)
                    cmd.CommandType = CommandType.StoredProcedure

                    Dim pSyncID As New SqlParameter("@SyncID", Data.SqlDbType.NVarChar, 5)
                    pSyncID.Value = SyncID.ToString
                    cmd.Parameters.Add(pSyncID)

                    Dim pLastRecordDateTime As New SqlParameter("@LastRecordDateTime", Data.SqlDbType.NVarChar, 50)
                    pLastRecordDateTime.Value = LastRecordDateTime.ToString()
                    cmd.Parameters.Add(pLastRecordDateTime)

                    Dim pResponse As New SqlParameter("@Response", Data.SqlDbType.NVarChar, 4000)
                    pResponse.Value = Response.Replace("'", "''")
                    cmd.Parameters.Add(pResponse)

                    Dim rowsAffected As New SqlParameter("@RecordsAffected", Data.SqlDbType.Int)
                    rowsAffected.Direction = Data.ParameterDirection.Output
                    cmd.Parameters.Add(rowsAffected)

                    cmd.ExecuteNonQuery()

                    If rowsAffected.Value < 1 Then
                        Throw New ArgumentOutOfRangeException
                    End If
                End Using
            End Using
        Catch AOORex As ArgumentOutOfRangeException
            'We're here because we didn't update any rows
            System.Diagnostics.EventLog.WriteEntry("VOEIS Hub Push Service", "No rows updated after running the command 'EXEC dbo.usp_UpdateSyncMetadata'.", EventLogEntryType.Error)
        Catch ex As Exception
            'We're here because something bad happened
            System.Diagnostics.EventLog.WriteEntry("VOEIS Hub Push Service", "Error in DataWriter class." & Environment.NewLine & ex.Message.ToString, EventLogEntryType.Error)

            'Email the exception and setup the current state
            Dim mailer As New SendMail()
            mailer.SendServiceMail("VOEIS Hub Push Service - Error", "Error in DataWriter class." & Environment.NewLine & ex.Message.ToString)
        End Try
    End Sub

    Private Sub GetServiceLastRecordDateTime()
        Dim StreamMetadata As New ArrayList()

        Using conn As New SqlConnection(_ServiceConnectionString)
            conn.Open()
            Using cmd As New SqlCommand("SELECT LastRecordDateTime FROM dbo.VOEISHubSync WHERE SyncID = " & _SyncID, conn)
                cmd.CommandTimeout = ServiceDBTimeout

                Dim reader As SqlDataReader = Nothing
                reader = cmd.ExecuteReader()

                'set the LastRecordDateTime
                While reader.Read()
                    _ServiceLastRecordDateTime = reader(0).ToString()
                End While
            End Using
        End Using
    End Sub
End Class
